<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/8/7
 * Time: 9:48
 */

namespace Customer\Controllers;


use Admin\Models\crm\XhcustInfo;
use Helpers\Assets;
use Helpers\Tool;

class StatsController extends ControllerBase
{

    /**
     * 客户报表
     *
     * @return \Phalcon\Mvc\View
     *
     * create by dale  20180807
     */
    public function statsCustomerAction(){
        try {
            $params = [
                'province'   => $this->request->getQuery('province', 'string'),
                'schoolType' => $this->request->getQuery('schoolType', 'string'),
                'startTime'  => $this->request->getQuery('startTime', 'string'),
                'endTime'    => $this->request->getQuery('endTime', 'string')
            ];
            $params = array_filter($params);
            $this->view->pick('stats/statsCustomer');
            Assets::importJs('js/stats/statsCustomer.js');
            Assets::excelLentExport();
            $sql = "select distinct(sProvinceName) as province  from  XHCust_Info where bDelete = 0 and iSchoolId is not null and sProvinceName is not null and sProvinceName <> ''  ";
            $provinceArr = array_column($this->db->fetchAll($sql), 'province');

            return $this->view->setVars([
                'info' => $this->getStatsCustomer($params),
                'fileName' => '客户报表(总)_' . date('Y-m-d'),
                'province' => array_merge($provinceArr, [0 => '无省']),
                'schoolType' => [1 => '意向学校', 2 => '合作学校', 3 => '前期接触',4 => '终止合作'],
                'params' => $params
            ]);
        } catch (\Exception $e) {
            return $this->sendViewError($e->getMessage());
        }
    }

    /**
     * 城市详情
     *
     * @return \Phalcon\Mvc\View|string
     *
     * create by dale  20180807
     */
    public function cityDetailAction()
    {
        try {
            $params = [
                'province'   => $this->request->getQuery('province', 'string'),
                'schoolType' => $this->request->getQuery('schoolType', 'string'),
                'startTime'  => $this->request->getQuery('startTime', 'string'),
                'endTime'    => $this->request->getQuery('endTime', 'string')
            ];
            $params = array_filter($params);
            $cityName = $this->request->getQuery('city');
            $this->view->pick('stats/cityDetail');
            Assets::excelLentExport();
            Assets::importJs('js/stats/cityDetail.js');
            if (!$cityName) {
                return $this->sendViewError('缺少市名');
            };
            $params['city'] = $cityName;
            return $this->view->setVars([
                'info' => $this->getStatsCustomer($params),
                'fileName' => '客户报表(' . $cityName . ')_' . date('Y-m-d'),
            ]);
        } catch (\Exception $e) {
            return $this->sendViewError($e->getMessage());
        }
    }


    /**
     * 获取客户报表数据
     *
     * @param  $params
     *
     * @return array
     * @throws \Exception
     *
     * create by dale  20180807
     */
    public function getStatsCustomer($params = [])
    {
        try {
            $sql = "select iSchoolId, IF(IFNULL(sProvinceName,'')<>'',sProvinceName, '无省') as sProvinceName,
                      IF(IFNULL(sCityName,'')<>'' ,sCityName,'无市') as sCityName,
                      sSchoolName from XHCust_Info where bDelete = 0 and iSchoolId is not null ";
            if ($params) {
                $sCityName     = Tool::getValue($params, 'city', null);
                $sProvinceName = Tool::getValue($params, 'province', null);
                $iType         = Tool::getValue($params, 'schoolType', null);
                $startTime     = Tool::getValue($params, 'startTime', null);
                $endTime       = Tool::getValue($params, 'endTime', null);
                if ($sCityName) {
                    if ($sCityName == '无市') {
                        $sql .= " and sCityName = '' ";
                    } else {
                        $sql .= " and sCityName = '{$sCityName}' ";
                    }
                }
                if ($sProvinceName) {
                    if ($sProvinceName == '无省') {
                        $sql .= " and sProvinceName = '' ";
                    } else {
                        $sql .= " and sProvinceName = '{$sProvinceName}' ";
                    }
                }
                if ($iType) {
                    $iType = $iType -1;
                    $sql .= " and iType = '{$iType}' ";
                }
                if ($startTime && $endTime) {
                    $sql .= " and tCoopDate >= '{$startTime}' and tCoopDate <= '{$endTime}' ";
                }
            } else {
                $sCityName = null;
            }
            $info = $this->db->fetchAll($sql);
//            var_dump($info);die;
            $schoolIds = implode(',', array_values(array_filter(array_column($info, 'iSchoolId'))));
            if (!$schoolIds) {
                return [];
            }
            $sql = "select count(distinct(sAdClsName)) as classNum , iSchoolId from XHSys_User where (sAdClsName is not null and sAdClsName <> '' ) and iUserType = 1 and bHistory = 0 AND bDelete = 0 AND bDisable = 0 and iSchoolId in ({$schoolIds}) group by iSchoolId ";
            $classNum = array_column($this->db->fetchAll($sql), 'classNum','iSchoolId');
            $sql = "select count(*) as stuNum , iSchoolId from XHSys_User where  iUserType = 1 and bHistory = 0 AND bDelete = 0 AND bDisable = 0 and iSchoolId in ({$schoolIds}) group by iSchoolId ";
            $stuNum = array_column($this->db->fetchAll($sql), 'stuNum', 'iSchoolId');
            $sql = "select count(*) as tchNum , iSchoolId from XHSys_User where  iUserType = 4 and bHistory = 0 AND bDelete = 0 AND bDisable = 0 and iSchoolId in ({$schoolIds}) group by iSchoolId ";
            $tchNum = array_column($this->db->fetchAll($sql), 'tchNum', 'iSchoolId');
            if (!$sCityName) {
                $mergeArr = [];
                foreach ($info as $index => $row) { //一维处理
                    $key = $row['sProvinceName'] . ',' . $row['sCityName'];
                    $mergeArr[$key] = Tool::getValue($mergeArr , $key , ['classNum' => 0 ,'stuNum' => 0 ,'tchNum' => 0 ,'schNum' => 0]);
                    $mergeArr[$key]['classNum'] += isset($classNum[$row['iSchoolId']]) ? intval($classNum[$row['iSchoolId']]) : 0;
                    $mergeArr[$key]['stuNum'] += isset($stuNum[$row['iSchoolId']]) ? intval($stuNum[$row['iSchoolId']]) : 0;
                    $mergeArr[$key]['tchNum'] += isset($tchNum[$row['iSchoolId']]) ? intval($tchNum[$row['iSchoolId']]) : 0;
                    $mergeArr[$key]['schNum'] += 1;
                }
                $finalArr = [];
                $total = [
                    'schNum'   => 0,
                    'classNum' => 0,
                    'tchNum'   => 0,
                    'stuNum'   => 0
                ];
                foreach ($mergeArr as $key => $row) { //多维展开
                    $keyArr = explode(',', $key);
                    $total['schNum'] += $row['schNum'];
                    $total['classNum'] += $row['classNum'];
                    $total['tchNum'] += $row['tchNum'];
                    $total['stuNum'] += $row['stuNum'];
                    $finalArr[$keyArr[0]][$keyArr[1]] = $row;
                }
                $finalArr['合计'][''] =  $total;
                return $finalArr;
            } else {
                $finalArr = [];
                $sql = "select sAdClsName as className , iSchoolId , COUNT(*) as stuNum  from XHSys_User where (sAdClsName is not null and sAdClsName <> '' ) and iUserType = 1 and bHistory = 0 AND bDelete = 0 AND bDisable = 0 and iSchoolId in ({$schoolIds}) group by  iSchoolId , sAdClsName ";
                $classes =  $this->db->fetchAll($sql);
                $grade = [];
                foreach ($classes as $class) {
                    $grade[$class['iSchoolId']][mb_substr($class['className'], 0, 2, 'utf-8')][] = $class;
                }
                $total = [
                    'schNum'   => 0,
                    'classNum' => 0,
                    'tchNum'   => 0,
                    'stuNum'   => 0
                ];
                foreach ($grade as $schoolId => $rows) {  //降维
                    foreach ($rows as $gradeId => $gradeNameArr) {
                        $grade[$schoolId][$gradeId] = [
                            'classes'    => implode(',', array_column($gradeNameArr, 'className')),
                            'classesNum' => count($gradeNameArr),
                            'stuNum'     => array_sum(array_column($gradeNameArr, 'stuNum'))
                        ];
                        $total['classNum'] += count($gradeNameArr);
                        $total['stuNum'] += array_sum(array_column($gradeNameArr, 'stuNum'));
                    }
                }

                foreach ($info as $index => $row) { //一维处理
                    $finalArr[$index] = [
                        'tchNum' => isset($tchNum[$row['iSchoolId']]) ? intval($tchNum[$row['iSchoolId']]) : 0,
                        'grade'  => isset($grade[$row['iSchoolId']]) ? $grade[$row['iSchoolId']] : [],
                        'schoolName' => $row['sSchoolName'],
                        'schoolId'   => $row['iSchoolId']
                    ];
                    $total['tchNum'] += isset($tchNum[$row['iSchoolId']]) ? intval($tchNum[$row['iSchoolId']]) : 0;
                    $total['schNum'] +=1;
                }
                $finalArr[] = array_merge(['schoolName' => '合计'], $total);
                return $finalArr;
            }
        } catch (\Exception $e) {
            throw  new \Exception($e->getMessage());
        }
    }




}